﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using ChineseSchool.Models.Hethong;
using System.Data.SqlClient;

namespace ChineseSchool.BLL
{
    public class B_News
    {
        private PetaPoco.Database db;
        public B_News()
        {
            db = new PetaPoco.Database("ConnectionString");
        }

        // Select All
        public DataTable GetAll()
        {
            DataTable dt = new DataTable();
            dt = db.ExecuteReader("EXEC News_Get_List");
            return dt;
        }

        // Select by ID
        public M_News Selectby_id(int NewsID)
        {
            var loadmenu = db.SingleOrDefault<M_News>("SELECT * FROM News bc WHERE bc.NewsID = " + NewsID + ";");
            db.CloseSharedConnection();
            return loadmenu;
        }

        public List<M_News> GetBy_MenuID(int MenuID)
        {
            var News = db.Fetch<M_News>(@";EXEC News_GetByMenuId @@MenuId = @0", MenuID);
            return News;
        }

        public List<M_News> GetBy_NewsTypeID(int NewsTypeID)
        {
            var News = db.Fetch<M_News>(@";EXEC News_GetByNewTypeId @@NewsTypeId = @0", NewsTypeID);
            return News;
        }

        public List<M_News> GetHotnews()
        {
            var News = db.Fetch<M_News>(@";EXEC News_GetHotNews");
            return News;
        }

        public List<M_News> GetTop10News(int NumberTop)
        {
            var News = db.Fetch<M_News>(@";EXEC News_Get_Top @@NumberTop = @0", NumberTop);
            return News;
        }

        public DataSet GetPhanTrang_DataSet(string strTenStore, int currentPage, int recordPerpage, int pageSize, int NewsTypeID)
        {
            DataSet ds = new DataSet();
            ds = db.Query<DataSet>(string.Format(";exec {0} {1}, {2}, {3}, {4}", strTenStore, currentPage, recordPerpage, pageSize, NewsTypeID)).FirstOrDefault();
            return ds;
        }

        public DataSet GetPhanTrangDataSet(string strTenStore, int currentPage, int recordPerpage, int pageSize, int NewsTypeID)
        {
            string strConnect = @"Server =123.30.185.47;uid=tiengtrungminha;pwd=admin@123;";
            DataSet ds = new DataSet();
            SqlConnection sqlCn = new SqlConnection(strConnect);
            try
            {
                //Mo ket noi
                sqlCn.Open();
                SqlCommand sqlCmd = new SqlCommand();
                sqlCmd.Connection = sqlCn;
                sqlCmd.CommandType = CommandType.StoredProcedure;
                sqlCmd.CommandText = strTenStore;
                sqlCmd.Parameters.Add(new SqlParameter("@currPage", currentPage));
                sqlCmd.Parameters.Add(new SqlParameter("@recodperpage", recordPerpage));
                sqlCmd.Parameters.Add(new SqlParameter("@Pagesize", pageSize));
                sqlCmd.Parameters.Add(new SqlParameter("@NewsTypeID", NewsTypeID));
                SqlDataAdapter sqlDa = new SqlDataAdapter();
                sqlDa.SelectCommand = sqlCmd;
                sqlDa.Fill(ds);
            }
            catch { }
            finally
            {
                if (sqlCn.State == ConnectionState.Open)
                    sqlCn.Close();
                sqlCn.Dispose();
            }
            return ds;
        }
    }
}